﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using AlexandriaPressAdminObject;

namespace AlexandriaPressAdminDataAccessLayer
{
    public class MostPopularDL
    {
        private static string myConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString;
            }
        }


        public static List<MostPopularObjects> PopularBookList(DateTime start, DateTime end)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_Most_Popular_Book";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.DateTime;
            param1.Value = start;
            param1.ParameterName = "@dateStart";
            param1.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.DbType = DbType.DateTime;
            param2.Value = end;
            param2.ParameterName = "@dateEnd";
            param2.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param2);

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<MostPopularObjects> lstOfbook = new List<MostPopularObjects>();
            MostPopularObjects book = null;

            while (dtReader.Read())
            {
                book = new MostPopularObjects();
                book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));
                book.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));
                book.Name = dtReader.GetString(dtReader.GetOrdinal("Author Name"));
                book.Total = dtReader.GetInt32(dtReader.GetOrdinal("Total"));
                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));
                lstOfbook.Add(book);
            }
            return lstOfbook;
        }

        public static List<MostPopularObjects> PopularAuthorList(DateTime start, DateTime end)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_Most_Popular_Author";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.DateTime;
            param1.Value = start;
            param1.ParameterName = "@dateStart";
            param1.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.DbType = DbType.DateTime;
            param2.Value = end;
            param2.ParameterName = "@dateEnd";
            param2.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param2);

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);
 
            List<MostPopularObjects> lstOfAuthor = new List<MostPopularObjects>();
            MostPopularObjects author = null;

            while (dtReader.Read())
            {
                author = new MostPopularObjects();
                author.AuthorID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));
                author.ProfilePicture = dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));
                author.Name = dtReader.GetString(dtReader.GetOrdinal("Author Name"));
                author.Total = dtReader.GetInt32(dtReader.GetOrdinal("Total"));
                lstOfAuthor.Add(author);
            }
            return lstOfAuthor;
        }

        public static int updatePopularBook(List<MostPopularObjects> popularBookList)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlTransaction transaction = conn.BeginTransaction();
            int i = 0;
            try
            {
                SqlCommand truncateTable = new SqlCommand();
                truncateTable.Connection = conn;
                truncateTable.CommandType = CommandType.StoredProcedure;
                truncateTable.CommandText = "usp_Truncate_Popular_Book";
                truncateTable.Transaction = transaction;
                

                truncateTable.ExecuteScalar();

                foreach (MostPopularObjects book in popularBookList)
                {
                    SqlCommand updateBook = new SqlCommand();
                    updateBook.Connection = conn;
                    updateBook.CommandType = CommandType.StoredProcedure;
                    updateBook.CommandText = "usp_Update_Popular_Book";
                    updateBook.Transaction = transaction;

                    SqlParameter param1 = new SqlParameter();
                    param1.DbType = DbType.Int32;
                    param1.Value = book.BookID;
                    param1.ParameterName = "@bookID";
                    param1.Direction = ParameterDirection.Input;
                    updateBook.Parameters.Add(param1);

                    SqlParameter param4 = new SqlParameter();
                    param4.DbType = DbType.Int32;
                    param4.Value = book.Total;
                    param4.ParameterName = "@bookSold";
                    param4.Direction = ParameterDirection.Input;
                    updateBook.Parameters.Add(param4);

                    updateBook.ExecuteScalar();

                }

                transaction.Commit();
                i = 1;
            }

            catch
            {
                transaction.Rollback();
                i = 0;
            }
            finally
            {
                conn.Close();
                transaction.Dispose();
                
            }

            return i;




        }





       

            
    }
}
